package cn.lcl.dao.impl;

import cn.lcl.bean.PageBean;
import cn.lcl.bean.ThreadLocalPageBean;
import cn.lcl.dao.IStudentDao;
import cn.lcl.bean.po.Student;
import cn.lcl.util.JdbcUtils;
import cn.lcl.util.Session;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by licho on 2017/8/15 0015.
 */
public class StudentDaoImpl implements IStudentDao {
    @Override
    public Long add(Student vo) throws SQLException {
        Long id=null;
        Session session=JdbcUtils.getThreadSession();
        String sql="begin insert into T_STUDENT(SID,NAME,TIME,CID) VALUES(sq_t_student.nextVal,?,?,?)" +
                "returning SID into ?; end;";
        id=(Long)session.getQuery().insert(sql,true,vo.getName(),vo.getTime(),vo.getCid());
        return id;
    }

    @Override
    public boolean delete(Long key) throws SQLException {
        boolean flag=false;
        Session session=JdbcUtils.getThreadSession();
        String sql="delete from T_STUDENT where SID=?";
        String sql1="delete from T_STUDENT_COURSE where SID=?";
        session.getQuery().update(sql1,key);
        int result=session.getQuery().update(sql,key);
        if(result>0)
            flag=true;
        return flag;
    }

    @Override
    public Student selectByKey(Long key) throws SQLException {
        Student result=null;
        Session session=JdbcUtils.getThreadSession();
        String sql="select *from T_STUDENT where SID=?";
        result=session.getQuery().query(sql,new BeanHandler<Student>(Student.class),key);
        return result;
    }

    @Override
    public PageBean<Student> getPageList() throws SQLException {
        Integer pageIndex = ThreadLocalPageBean.getPageIndex();
        Integer pageSize = ThreadLocalPageBean.getPageSize();
        Integer beginIndex=(pageIndex-1)*pageSize+1;
        Integer endIndex=pageIndex*pageSize;
        List<Student> list=null;
        Session session=JdbcUtils.getThreadSession();
        String countSql="select count(*) as totals from T_STUDENT";
        Map<String,Object> result= session.getQuery().query(countSql,new MapHandler());
        Object obj=result.get("totals");
        Integer c= (Integer) ConvertUtils.convert(obj,Integer.class);
        String sql="select * from(select A.*,ROWNUM RN from (select *from T_STUDENT order by sid asc) A where"
                + " ROWNUM <= ?) where RN >= ?";
        list=session.getQuery().query(sql,new BeanListHandler<Student>(Student.class),endIndex,beginIndex);
        PageBean bean =new PageBean(pageIndex,pageSize,c,list);
        return bean;
    }

    @Override
    public boolean update(Student vo) throws SQLException {
        return false;
    }
}
